package beans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import util.ConnectionManager;
import util.SystemException;
import util.Utilities;

public class PaymentBean {
    private String userID;
    private String holdername;
    private String number;
    private String bank;
    private String issueDate;
    private String company;
    private String amount;
    private int paymentID;
    private String date;
    private String time;
    private String currency;
    
    public PaymentBean() {
    }
    
    public PaymentBean(String userID, String holdername, String number, String bank, String issueDate,
            String company, String amount, int paymentID, String date, String time, String currency) {
        this.userID = userID;
        this.holdername = holdername;
        this.number = number;
        this.bank = bank;
        this.issueDate = issueDate;
        this.company = company;
        this.amount = amount;
        this.paymentID = paymentID;
        this.date = date.split("\\s+")[0];
        this.time = time.split("\\s+")[1];
        this.currency = currency;
        
    }
    
    public static void deletePayment(final String paymentID) {
        Connection con = null;
        try {
            con = ConnectionManager.connect();
            PreparedStatement update = con.prepareStatement("DELETE FROM payment WHERE paymentID=?;");
            
            update.setString(1, paymentID);
            update.execute();
            con.commit();
            
        } catch (SQLException e) {
            e. printStackTrace();
        } finally {
            Utilities.close(con);
        }
    }
    public static PaymentBean oneSetOfResultSetToPayment(final ResultSet rs) throws SQLException {
        String userID = rs.getString("userID");
        String holdername = rs.getString("holdername");
        String number = rs.getString("number");
        String bank = rs.getString("bank");
        String issueDate = rs.getString("issueDate");
        String company = rs.getString("company");
        String amount = rs.getString("amount");
        int paymentID = rs.getInt("paymentID");
        String date = rs.getString("date");
        String time = rs.getString("time");
        String currency = rs.getString("currency");
        PaymentBean card = new PaymentBean(userID, holdername, number, bank, issueDate, company, amount, paymentID, date, time, currency);
        return card;
    }
    static PaymentBean[] payment(String sql) {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        PaymentBean[] finalResult = new PaymentBean[0];
        try {
            con = ConnectionManager.connect();
            //String query = ("select * from payment WHERE date >= #"+start+"# AND date <=#"+end+"#");
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            List<PaymentBean> result = new ArrayList<PaymentBean>();
            while(rs.next()) {
                PaymentBean card = oneSetOfResultSetToPayment(rs);
                result.add(card);
            }
            finalResult = (PaymentBean[])result.toArray(finalResult);
        }catch (Exception e) {
            
        }finally {
            Utilities.close(rs, stmt, con);
        }
        return finalResult;
    }
    
    public static int getTotalRecordCount() throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        
        try {
            con = ConnectionManager.connect();
            String query = "select count(paymentID) as no from payment";
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            
            while(rs.next()) {
                int count = rs.getInt("no");
                return count;
            }
            
        }catch (Exception e) {
            throw new SystemException("Database Service is not availiable for a moment.");
        }finally {
            Utilities.close(rs, stmt, con);
        }
        return 0;
    }
    
    public static PaymentBean[] allPayment() {
        return payment("select * from payment");
    }
    public static PaymentBean[] byDate(String start, String end) {
        return payment("select * from payment WHERE date >= #"+start+"# AND date <=#"+end+"#");
    }
    
    public static PaymentBean[] byHolder(String name) {
        return payment("select * from payment WHERE holdername ='"+name+"'");
    }
    
    public static PaymentBean[] byUser(String name) {
        return payment("select * from payment WHERE userID ='"+name+"'");
    }
    public static ModelBean[] getModels(final int paymentID) throws SystemException {
        return ModelBean.model(
                "SELECT model.itemID AS itemID, paymentID, model.name AS name, model.type AS type, model.company AS company,"+
                "model.scale AS scale, model.price AS price, model.description AS description, model.imageURL AS imageURL, "+
                "payment_advance.quantity AS quantity3 "+
                "FROM model, payment_advance "+
                "WHERE "+paymentID+" = payment_advance.paymentID AND payment_advance.itemID = model.itemID");
    }
    
    public synchronized static String createPayment(String userID, HttpServletRequest request) {
        
        Connection con = null;
        try {
            
            String holdername = request.getParameter("holdername");
            String number = request.getParameter("number");
            String bank = request.getParameter("bank");
            String issueDate = request.getParameter("issueDate");
            String company = request.getParameter("company");
            String amount = request.getParameter("amount");
            String currency = request.getParameter("currency");
            String address = request.getParameter("address");
            
            con = ConnectionManager.connect();
            
            // self-create Payment ID
            String query = "SELECT Max(paymentID) as max FROM payment";
            ResultSet rs = con.createStatement().executeQuery(query);
            int paymentID = 0;
            if (!rs.next()) {
                paymentID = 1;
            } else {
                paymentID = rs.getInt("max")+1;
            }
            
            // INSERT payment information
            PreparedStatement update = con.prepareStatement("INSERT INTO payment VALUES (?, ?, ?, ?, ?, ?, ?, ?, date(), time(), ?, ?)");
            update.setInt(1, paymentID);
            update.setString(2, userID);
            update.setString(3, holdername);
            update.setString(4, number);
            update.setString(5, bank);
            update.setString(6, company);
            update.setString(7, issueDate);
            update.setString(8, amount);
            update.setString(9, currency);
            update.setString(10, address);
            update.executeUpdate();
            
            //cookies for getting itemID and Quantity
            Cookie[] cookies = request.getCookies();
            
            for (int i=0; i<cookies.length; i++) {
                Cookie myCookie = cookies[i];
                String name = myCookie.getName();
                if ( name.indexOf(".ID") != -1) {
                    String itemID = myCookie.getValue();
                    for (int j=0; j<cookies.length; j++) {
                        myCookie = cookies[j];
                        String value = myCookie.getValue();
                        name = myCookie.getName();
                        if ( name.indexOf(itemID) != -1) {
                            if ( name.indexOf("Quantity") != -1) {
                                int sellQuantity = Integer.parseInt(value);
                                String sql = "SELECT quantity3 as qq FROM model WHERE itemID ="+itemID+"";
                                ResultSet rs1 = con.prepareStatement(sql).executeQuery();
                                rs1.next();
                                int originalQuantity = (int)rs1.getDouble("qq");
                                if (originalQuantity >= sellQuantity) {
                                    String updateModel = "UPDATE model SET quantity3='"+(originalQuantity-sellQuantity)+"' WHERE itemID="+itemID+"";
                                    con.createStatement().executeUpdate(updateModel);
                                    String updateAdvance = "INSERT INTO payment_advance VALUES ("+paymentID+", "+itemID+","+sellQuantity+")";
                                    con.createStatement().executeUpdate(updateAdvance);
                                }else {
                                    String key = itemID+".Name";
                                    for (int z=0; z<cookies.length; z++) {
                                        Cookie myCookie2 = cookies[z];
                                        if (myCookie2.getName().equals(key)) {
                                            String itemName = myCookie2.getValue();
                                            return itemName;
                                        }
                                    }
                                }
                            }
                        }
                        
                    }
                }
            }
            
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            Utilities.close(null, null, con);
        }
        //Clear the shopping cart after payment!
        Cookie[] cookies = request.getCookies();
        for (int i=0; i<cookies.length; i++) {
            Cookie myCookie = cookies[i];
            String name = myCookie.getName();
            if ( name.indexOf(".ID") != -1) {
                String itemID = myCookie.getValue();
                for (int j=0; j<cookies.length; j++) {
                    myCookie = cookies[j];
                    String value = myCookie.getValue();
                    name = myCookie.getName();
                    if ( name.indexOf(itemID) != -1) {
                        // REMOVE THE COOKIE OF THAT PURCHASED PRODUCT
                        myCookie.setMaxAge(0);
                    }
                }
            }
        }
        return null;
    }
    
    public String getUserID() {
        return userID;
    }
    
    public String getHoldername() {
        return holdername;
    }
    
    public String getNumber() {
        return number;
    }
    
    public String getBank() {
        return bank;
    }
    
    public String getIssueDate() {
        return issueDate;
    }
    
    public String getCompany() {
        return company;
    }
    
    public String getAmount() {
        return amount;
    }
    
    public int getPaymentID() {
        return paymentID;
    }
    
    public String getDate() {
        return date;
    }
    
    public String getTime() {
        return time;
    }
    
    public String getCurrency() {
        return currency;
    }
    
    
    
    
}
